const query = require('../models');
var studentcontrollers = {
    get: function (req, res) {
      let {page, lis} = req.query;
   
      page = Number(page);
      lis  = Number(lis );
      let start = (page - 1) * lis ;
      const sql =`
      SELECT NAME AS 'name',gender,id, phone,Email,Dates,class_name AS class
      FROM students 
      JOIN classes  
      ON students.class_id=classes.class_id
      WHERE is_del = 1
      limit ?,?
      `;
      query(sql,[start, lis])
        .then(function(result) {
          
          res.json({
            code: '200',
            msg: '查询成功',
            data: result
          });
        })
        .catch(function(err) {
          res.json({
            code: '1002',
            msg: '失败',
            err
          })
        })
    },
    del: function (req, res) {
      const sql =`
      update students set is_del=0 where id=?
      `;
      query(sql,[req.body.Id1])
        .then(function(result) {
          console.log('删除成功：', result);
          res.json({
            code: '200',
            msg: '是否确定删除',
          });
        })
        .catch(function(err) {
          res.json({
            code: '1002',
            msg: '失败',
            err:err
          })
        })
    },
    add(req, res) {

    },
    revamp(req, res) {
      const sql =`
      UPDATE students SET NAME=?,gender=? ,class_id =2, phone=?,Email=?,Dates=? WHERE id = ?;
      `;
      let {nowname, nowclass, nowgender,nowDates, nowphone, nowEmail, userId } = req.body;
      // req.body.item.name,req.body.item.gender,req.body.item.class,req.body.item.phone,req.body.item.Email,req.body.userId 
        query(sql,[nowname,nowgender,nowphone,nowEmail,nowDates,userId])
        .then(function(result) {  
          res.json({
            code: '200',
            msg: '查询成功',
            result
          });
        })
        .catch(function(err) {
          res.json({
            code: '1002',
            msg: '失败',
            err
          })
        })
    },
    revamp1(req, res) {
      const sql =`
      UPDATE studentscent SET years=?,introduce=?,address=? ,hobby =?, Awards=?,parentname=? ,parentphone=? WHERE id = ?;
      `;
      let {nowyears, nowintroduce,nowaddress,nowhobby,nowAwards,nowparentname,nowparentphone, userId} = req.body;
      // req.body.item.name,req.body.item.gender,req.body.item.class,req.body.item.phone,req.body.item.Email,req.body.userId 
        query(sql,[nowyears, nowintroduce,nowaddress,nowhobby,nowAwards,nowparentname,nowparentphone, userId])
        .then(function(result) {  
          res.json({
            code: '200',
            msg: '查询成功',
            result
          });
        })
        .catch(function(err) {
          res.json({
            code: '1002',
            msg: '失败',
            err
          })
        })
    },
    details(req, res) {
      const sql =`
        SELECT id,name,gender ,years,class_name class, phone,Email,address,Dates,introduce,hobby,Awards, parentname,parentphone
        FROM (SELECT * FROM students LEFT JOIN studentscent  USING(id))AS list1 
        LEFT JOIN classes USING(class_id)
        WHERE id = ?;
      `;
      console.log(req.body.userId)
      query(sql,[req.body.userId])
        .then(function(result) {
          res.json({
            code: '200',
            msg: '查询成功',
            data: result[0]
          });
        })
        .catch(function(err) {
          res.json({
            code: '1002',
            msg: '失败',
            err
          })
        })
    },
    total(req, res) {
      const sql = `
      SELECT COUNT(*) total FROM students WHERE is_del=1;
      `
      query(sql,null)
        .then(function(result) {
          res.json({
            code: '200',
            msg: '查询成功',
            data: result[0]
          });
        })
        .catch(function(err) {
          res.json({
            code: '1002',
            msg: '失败',
            err
          })
        })
    },
    selectAll(req, res) {
      const sql = `
      UPDATE students SET state=?
      `
      query(sql,[req.query.selectAllstate])
        .then(function(result) {
          res.json({
            code: '200',
            msg: '全选成功',
          });
        })
        .catch(function(err) {
          res.json({
            code: '1002',
            msg: '失败',
            err
          })
        })
    },
    select(req, res) {
      const sql = `
      UPDATE students SET state=? WHERE id=?
      `
      query(sql,[req.query.selectstate,req.query.Id1])
        .then(function(result) {
          res.json({
            code: '200',
            msg: '全选成功',
          });
        })
        .catch(function(err) {
          res.json({
            code: '1002',
            msg: '失败',
            err
          })
        })
    },
    selectnum(req, res) {
      let {page, lis} = req.query;
   
      page = Number(page);
      lis  = Number(lis );
      let start = (page - 1) * lis ;
      const sql = `
      SELECT NAME,class,gender,id, phone,Email,Dates,state
      FROM students 
      WHERE is_del = 1 AND state='true'
      LIMIT ?,?
      `
      query(sql,[start, lis])
        .then(function(result) {
          res.json({
            code: '200',
            msg: '全选成功',
            data: result
          });
        })
        .catch(function(err) {
          res.json({
            code: '1002',
            msg: '失败',
            err
          })
        })
    }
  }
  
  module.exports = studentcontrollers;